﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.Data.Schema.SchemaModel;
using Microsoft.Data.Schema.ScriptDom.Sql;
using Microsoft.Data.Schema.Sql.SchemaModel;
using Microsoft.Data.Schema.Sql;
using Microsoft.Data.Schema.StaticCodeAnalysis;


namespace Neznayka
{
    class ModelIndexAndKeysUtils
    {

        public static List<Int32> getCorrespondingKeyPositions(List<String> SearchedForKeys, List<String> SearchedLocation)
        {
            // Look for the columns in SearchedForKeys in the list of columns SearchedLocation.
            String[] ar = SearchedLocation.ToArray();

            List<Int32> res = new List<Int32>();
            foreach (var sk in SearchedForKeys)
            {
                // if we find the col in the SearchedLocation, record its position (o-based) in res.
                Int32 pos = Array.FindIndex(ar, mem => SqlComparer.CompareEqual(sk, mem));
                res.Add(pos);
            }
            return res;
        }


        public static List<ISqlForeignKeyConstraint> getForeignKeys(string owningObjectSchema, string owningObjectTable)
        {
            return DMVSettings.getForeignKeys
                                            .Where(n => (     n.Name                        == null
                                                        || (  n.Name.ExternalParts          == null
                                                           || n.Name.ExternalParts.Count    == 0
                                                           )
                                                        )
                                                        && SqlComparer.CompareEqual(n.DefiningTable.Name.Parts[0], owningObjectSchema)
                                                        && SqlComparer.CompareEqual(n.DefiningTable.Name.Parts[1], owningObjectTable)
                                                ).Select(n => n).ToList();

        }

        
        public static List<ISqlPrimaryKeyConstraint> getPrimaryKeys(string owningObjectSchema, string owningObjectTable)
        {
            return DMVSettings.getPrimaryKeys
                                            .Where(n => (     n.Name                        == null
                                                        || (  n.Name.ExternalParts          == null
                                                           || n.Name.ExternalParts.Count    == 0
                                                           )
                                                        )
                                                        && SqlComparer.CompareEqual(n.DefiningTable.Name.Parts[0], owningObjectSchema)
                                                        && SqlComparer.CompareEqual(n.DefiningTable.Name.Parts[1], owningObjectTable)
                                              ).Select(n => n).ToList();

        }
        public static List<ISqlPrimaryKeyConstraint> getClusteredPrimaryKeys(string owningObjectSchema, string owningObjectTable)
        {
            return DMVSettings.getPrimaryKeys
                                            .Where(n => (     n.Name                        == null
                                                        || (  n.Name.ExternalParts          == null
                                                           || n.Name.ExternalParts.Count    == 0
                                                           )
                                                        )
                                                        && SqlComparer.CompareEqual(n.DefiningTable.Name.Parts[0], owningObjectSchema)
                                                        && SqlComparer.CompareEqual(n.DefiningTable.Name.Parts[1], owningObjectTable)
                                                        && n.IsClustered
                                              ).Select(n => n).ToList();

        }

        public static List<ISqlIndex> getIndexes(string owningObjectSchema, string owningObjectTable)
        {
            return DMVSettings.getIndexes
                                             .Where(n => (n.Name.ExternalParts == null
                                                      || n.Name.ExternalParts.Count == 0
                                                      )
                                                   && SqlComparer.CompareEqual(n.IndexedObject.Name.Parts[0], owningObjectSchema)
                                                   && SqlComparer.CompareEqual(n.IndexedObject.Name.Parts[1], owningObjectTable)
                                               ).Select(n => n).ToList();

        }
        public static List<ISqlIndex> getClusteredIndexes(string owningObjectSchema, string owningObjectTable)
        {
            return DMVSettings.getIndexes
                                             .Where(n => (n.Name.ExternalParts == null
                                                      || n.Name.ExternalParts.Count == 0
                                                      )
                                                   && SqlComparer.CompareEqual(n.IndexedObject.Name.Parts[0], owningObjectSchema)
                                                   && SqlComparer.CompareEqual(n.IndexedObject.Name.Parts[1], owningObjectTable)
                                                   && n.IsClustered
                                               ).Select(n => n).ToList();

        }

        public static List<ISqlUniqueConstraint> getUniqueConstraints(string owningObjectSchema, string owningObjectTable)
        {
            return DMVSettings.getUniqueConstraints
                                    .Where(n => 
                                            (     n.Name                        == null 
                                            || (  n.Name.ExternalParts          == null
                                               || n.Name.ExternalParts.Count    == 0
                                               )
                                            )
                                            && SqlComparer.CompareEqual(n.DefiningTable.Name.Parts[0], owningObjectSchema)
                                            && SqlComparer.CompareEqual(n.DefiningTable.Name.Parts[1], owningObjectTable)
                                      ).Select(n => n).ToList();

        }
        public static List<ISqlUniqueConstraint> getClusteredUniqueConstraints(string owningObjectSchema, string owningObjectTable)
        {
            return DMVSettings.getUniqueConstraints
                                    .Where(n =>
                                            (     n.Name                        == null
                                            || (  n.Name.ExternalParts          == null
                                               || n.Name.ExternalParts.Count    == 0
                                               )
                                            )
                                            && SqlComparer.CompareEqual(n.DefiningTable.Name.Parts[0], owningObjectSchema)
                                            && SqlComparer.CompareEqual(n.DefiningTable.Name.Parts[1], owningObjectTable)
                                            && n.IsClustered
                                      ).Select(n => n).ToList();

        }

        public static List<String> getClusteredKeyColumns(string owningObjectSchema, string owningObjectTable)
        {
            //find clustered constraints - making sure we're not looking at ourselves when we do do !!!!
            List<String> ClusterColumns = new List<String>();

            List<ISqlPrimaryKeyConstraint> clusteredpks = DMVSettings.getPrimaryKeys
                                            .Where(n => (n.DefiningTable.Name.ExternalParts == null || n.DefiningTable.Name.ExternalParts.Count == 0)
                                                      && SqlComparer.CompareEqual(n.DefiningTable.Name.Parts[0], owningObjectSchema)
                                                      && SqlComparer.CompareEqual(n.DefiningTable.Name.Parts[1], owningObjectTable)
                                                      && n.IsClustered
                                                      ).Select(n => n).ToList();

            List<ISqlIndex> clusteredindexes = DMVSettings.getIndexes
                                                        .Where(n => (n.Name.ExternalParts == null
                                                                 || n.Name.ExternalParts.Count == 0
                                                                 )
                                                              && SqlComparer.CompareEqual(n.IndexedObject.Name.Parts[0], owningObjectSchema)
                                                              && SqlComparer.CompareEqual(n.IndexedObject.Name.Parts[1], owningObjectTable)
                                                              && n.IsClustered
                                                          ).Select(n => n).ToList();

            List<ISqlUniqueConstraint> uniqueClusterConstraints = DMVSettings.getUniqueConstraints
                                                        .Where(n =>
                                                                (     n.Name                        == null
                                                                || (  n.Name.ExternalParts          == null
                                                                   || n.Name.ExternalParts.Count    == 0
                                                                   )
                                                                )
                                                                && SqlComparer.CompareEqual(n.DefiningTable.Name.Parts[0], owningObjectSchema)
                                                                && SqlComparer.CompareEqual(n.DefiningTable.Name.Parts[1], owningObjectTable)
                                                                && n.IsClustered
                                                          ).Select(n => n).ToList();

            bool clusteredindexExists            = (clusteredindexes.Count > 0);
            bool clusteredUniqueConstraintExists = (uniqueClusterConstraints.Count > 0);
            bool clusteredPrimaryKeyExists       = (clusteredpks.Count > 0);


            if (clusteredPrimaryKeyExists || clusteredindexExists || clusteredUniqueConstraintExists)
            {
                if (clusteredPrimaryKeyExists)
                {
                    ISqlPrimaryKeyConstraint v = clusteredpks[0];
                    foreach (var c in v.ColumnSpecifications)
                    {
                        String lastElement = "";
                        foreach (var n in c.Column.Name.Parts)
                        {
                            lastElement = n;
                        }
                        ClusterColumns.Add(lastElement);
                    }
                }
                else if (clusteredindexExists)
                {
                    ISqlIndex v = clusteredindexes[0];
                    foreach (var c in v.ColumnSpecifications)
                    {
                        String lastElement = "";
                        foreach (var n in c.Column.Name.Parts)
                        {
                            lastElement = n;
                        }
                        ClusterColumns.Add(lastElement);
                    }
                }
                else if (clusteredUniqueConstraintExists)
                {
                    ISqlUniqueConstraint v = uniqueClusterConstraints[0];
                    foreach (var c in v.ColumnSpecifications)
                    {
                        String lastElement = "";
                        foreach (var n in c.Column.Name.Parts)
                        {
                            lastElement = n;
                        }
                        ClusterColumns.Add(lastElement);
                    }
                }

            }
            return ClusterColumns;
        }

    }
}
